
/*================================================
Part I. Share of wealth , by wealth groups
================================================*/

clear all

import delimited using "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/dfa_networth_bus_cycle2_shares.csv"
sort cat
gen double n=_n
by cat: gen double id = n[1]
drop n 		
gen year_str = substr(date, 1, 4)       // Extract year
gen year = real(year_str)   
gen quarter_str = substr(date, 7, .)    // Extract quarter as string
gen quarter = real(quarter_str) 
gen month = quarter * 3      
gen qtr = yq(year,quarter)	//change from monthly variable to quarterly
format %tq qtr
tsset id qtr         // Convert quarter to month


gen networth_2019q4 = .
replace networth_2019q4 = networth if qtr <= tq(2019q4)

gen networth_2019q3 = .
replace networth_2019q3 = networth if qtr <= tq(2019q3)


// Define a list of categories
local catlist "Bottom50 TopPt1 pct50to70 pct70to90 pct90to99 pct99to999"

local newvars ""

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen networth_`cat' = .
    replace networth_`cat' = networth if cat == "`cat'"
    local newvars `newvars' networth_`cat'
}

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen networth_2019q4_`cat' = .
    replace networth_2019q4_`cat' = networth_2019q4 if cat == "`cat'"
    local newvars `newvars' networth_2019q4_`cat'
}

foreach cat of local catlist {
    gen networth_2019q3_`cat' = .
    replace networth_2019q3_`cat' = networth_2019q3 if cat == "`cat'"
    local newvars `newvars' networth_2019q3_`cat'
}
di "`newvars'"

* Column 1
putexcel A1= "Shares"
tabstat networth_2019q4_Bottom50 networth_2019q4_pct50to70 networth_2019q4_pct70to90 networth_2019q4_pct90to99 networth_2019q4_pct99to999 networth_2019q4_TopPt1, stat(mean) save
return list
mat Table3PanBall = r(StatTotal)
mat rownames Table3PanBall = "Shares" 

gen expansion = .
*https://fred.stlouisfed.org/series/USRECQM
replace expansion = 0 if (year==2007 & qtr==4) | (year==2008) | (year==2009 & qtr<=2) | (year==2001) | (year==1990 & qtr>=3) | (year==1991 & qtr<=1) | (year==2019 & qtr==4) | (year==2020 & qtr==1)
replace expansion = 1 if missing(expansion)==1


foreach cat of local catlist {
    gen networth_2019q4_`cat'_rec = .
    replace networth_2019q4_`cat'_rec = networth_2019q4_`cat' if expansion == 0
    gen networth_2019q4_`cat'_exp = .
    replace networth_2019q4_`cat'_exp = networth_2019q4_`cat' if expansion == 1
}

tabstat networth_2019q4_Bottom50_rec networth_2019q4_pct50to70_rec networth_2019q4_pct70to90_rec networth_2019q4_pct90to99_rec networth_2019q4_pct99to999_rec networth_2019q4_TopPt1_rec, stat(mean) save
return list
mat Table3PanBrec = r(StatTotal)
mat rownames Table3PanBrec = "Shares" 

tabstat networth_2019q4_Bottom50_exp networth_2019q4_pct50to70_exp networth_2019q4_pct70to90_exp networth_2019q4_pct90to99_exp networth_2019q4_pct99to999_exp networth_2019q4_TopPt1_exp, stat(mean) save
return list
mat Table3PanBexp = r(StatTotal)
mat rownames Table3PanBexp = "Shares" 

/*================================================
Part II. Quarterly change in wealth levels as pct of previous level, by wealth groups
================================================*/

// Read in *levels* (get most recent data, just using the one I can find)
*import delimited using "/fof/dfa/projects/papers/business_cycles/data/output/aug2023/dfa_networth_bus_cycle2_levels.csv"
import delimited using "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/dfa_networth_bus_cycle2_levels.csv", clear

sort cat
gen double n=_n
by cat: gen double id = n[1]
drop n 		
gen year_str = substr(date, 1, 4)       // Extract year
gen year = real(year_str)   
gen quarter_str = substr(date, 7, .)    // Extract quarter as string
gen quarter = real(quarter_str) 
gen month = quarter * 3      
gen qtr = yq(year,quarter)	//change from monthly variable to quarterly
format %tq qtr
tsset id qtr         // Convert quarter to month

***** drop if 2020 or later
keep if year<=2019

// levels difference (qtrly)
by id: gen nwdiff = (networth-l.networth)
// levels pct change (qtrly)
by id: gen nwpct = 100* (networth-l.networth)/l.networth

replace cat="Bottom50" if cat=="pct0to50"
replace cat="TopPt1" if cat=="pct99.9to100"
replace cat="pct99to999" if cat=="pct99to99.9"

// Define a list of categories
*local catlist "pct0to50  pct50to70 pct70to90 pct90to99 pct99to99.9 pct99.9to100"
local catlist "Bottom50 TopPt1 pct50to70 pct70to90 pct90to99 pct99to999"

local newvars ""

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen nwdiff_`cat' = .
    replace nwdiff_`cat' = nwdiff if cat == "`cat'"
    local newvars `newvars' nwdiff_`cat'
}

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen nwpct_`cat' = .
    replace nwpct_`cat' = nwpct if cat == "`cat'"
    local newvars `newvars' nwpct_`cat'
}

tabstat  nwdiff_Bottom50 nwdiff_pct50to70 nwdiff_pct70to90 nwdiff_pct90to99 nwdiff_pct99to999 nwdiff_TopPt1, stat(n mean sd skewness kurtosis)
tabstat  nwpct_Bottom50 nwpct_pct50to70 nwpct_pct70to90 nwpct_pct90to99 nwpct_pct99to999 nwpct_TopPt1, stat(n mean sd skewness kurtosis)


putexcel set "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/PanelBx.xlsx", replace 

*Columns 2-5
putexcel C1= "NW_Pct_Changes"
putexcel C1:G1, border(bottom) merge hcenter
tabstat nwpct_Bottom50 nwpct_pct50to70 nwpct_pct70to90 nwpct_pct90to99 nwpct_pct99to999 nwpct_TopPt1, stat(n mean sd skewness kurtosis) save 
return list
mat Table3PanelB = r(StatTotal)
*putexcel A2= matrix(r(StatTotal)'), names nformat(number_d2)

*** autocorrelation

reg nwpct_Bottom50 l.nwpct_Bottom50
mat acB50=e(b)[1,1] 
reg nwpct_pct50to70 l.nwpct_pct50to70
mat acT5070=e(b)[1,1] 
reg nwpct_pct70to90 l.nwpct_pct70to90
mat acT7090=e(b)[1,1] 
reg nwpct_pct90to99 l.nwpct_pct90to99
mat acT10=e(b)[1,1] 
reg nwpct_pct99to999 l.nwpct_pct99to999
mat acT1=e(b)[1,1] 
reg nwpct_TopPt1 l.nwpct_TopPt1
mat acT01=e(b)[1,1] 
mat ac = acB50,acT5070,acT7090,acT10,acT1,acT01
mat rownames ac = "autocorr"
mat Table3PanelB = Table3PanBall\Table3PanelB\ac

putexcel A2= matrix(Table3PanelB'), names nformat(number_d2)


****************** Expansions and Recessions (testing, need to do better here...)

*egen row_number = seq(), from(1)

gen expansion = .
*https://fred.stlouisfed.org/series/USRECQM
replace expansion = 0 if (year==2007 & qtr==4) | (year==2008) | (year==2009 & qtr<=2) | (year==2001) | (year==1990 & qtr>=3) | (year==1991 & qtr<=1) | (year==2019 & qtr==4) | (year==2020 & qtr==1)
replace expansion = 1 if missing(expansion)==1

*gen expans_alt = .
* https://fred.stlouisfed.org/series/USRECQ
*replace expans_alt = 0 if (year==1990 & qtr==4) | (year==1991 & qtr==1) | (year==2001 & (qtr>=2 & qtr<=4)) | (year==2008) | (year==2009 & (qtr>=1 & qtr<=2)) | (year==2020 & (qtr>=1 & qtr<=2))
*replace expans_alt = 1 if missing(expans_alt)==1


foreach cat of local catlist {
    gen nwpct_`cat'_rec = .
    replace nwpct_`cat'_rec = nwpct_`cat' if expansion == 0
    gen nwpct_`cat'_exp = .
    replace nwpct_`cat'_exp = nwpct_`cat' if expansion == 1
}

tabstat  nwpct_Bottom50_rec nwpct_pct50to70_rec nwpct_pct70to90_rec nwpct_pct90to99_rec nwpct_pct99to999_rec nwpct_TopPt1_rec, stat(n mean sd skewness kurtosis)

tabstat  nwpct_Bottom50_exp nwpct_pct50to70_exp nwpct_pct70to90_exp nwpct_pct90to99_exp nwpct_pct99to999_exp nwpct_TopPt1_exp, stat(n mean sd skewness kurtosis)



putexcel I1= "Recession"
putexcel I1:P1, border(bottom) merge hcenter
tabstat nwpct_Bottom50_rec nwpct_pct50to70_rec nwpct_pct70to90_rec nwpct_pct90to99_rec nwpct_pct99to999_rec nwpct_TopPt1_rec, stat(n mean sd skewness kurtosis) save 
mat Table3PanelB = r(StatTotal)
return list
*putexcel H2= matrix(Table3PanelB'), names nformat(number_d2)

*** autocorrelation (recession)

reg nwpct_Bottom50 l.nwpct_Bottom50 if expansion == 0
mat acB50=e(b)[1,1] 
reg nwpct_pct50to70 l.nwpct_pct50to70 if expansion == 0
mat acT5070=e(b)[1,1] 
reg nwpct_pct70to90 l.nwpct_pct70to90 if expansion == 0
mat acT7090=e(b)[1,1] 
reg nwpct_pct90to99 l.nwpct_pct90to99 if expansion == 0
mat acT10=e(b)[1,1] 
reg nwpct_pct99to999 l.nwpct_pct99to999 if expansion == 0
mat acT1=e(b)[1,1] 
reg nwpct_TopPt1 l.nwpct_TopPt1 if expansion == 0
mat acT01=e(b)[1,1] 
mat ac = acB50,acT5070,acT7090,acT10,acT1,acT01
mat rownames ac = "autocorr"
mat Table3PanelB = Table3PanBrec\Table3PanelB\ac

putexcel I2= matrix(Table3PanelB'), names nformat(number_d2)

putexcel Q1= "Expansion"
putexcel Q1:X1, border(bottom) merge hcenter
tabstat nwpct_Bottom50_exp nwpct_pct50to70_exp nwpct_pct70to90_exp nwpct_pct90to99_exp nwpct_pct99to999_exp nwpct_TopPt1_exp, stat(n mean sd skewness kurtosis) save 
mat Table3PanelB = r(StatTotal)
return list
*putexcel P2= matrix(r(StatTotal)'), names nformat(number_d2)


*** autocorrelation (expansion)

reg nwpct_Bottom50 l.nwpct_Bottom50 if expansion == 1
mat acB50=e(b)[1,1] 
reg nwpct_pct50to70 l.nwpct_pct50to70 if expansion == 1
mat acT5070=e(b)[1,1] 
reg nwpct_pct70to90 l.nwpct_pct70to90 if expansion == 1
mat acT7090=e(b)[1,1] 
reg nwpct_pct90to99 l.nwpct_pct90to99 if expansion == 1
mat acT10=e(b)[1,1] 
reg nwpct_pct99to999 l.nwpct_pct99to999 if expansion == 1
mat acT1=e(b)[1,1] 
reg nwpct_TopPt1 l.nwpct_TopPt1 if expansion == 1
mat acT01=e(b)[1,1] 
mat ac = acB50,acT5070,acT7090,acT10,acT1,acT01
mat rownames ac = "autocorr"
mat Table3PanelB = Table3PanBexp\Table3PanelB\ac

*putexcel V2= matrix(Table3PanelB'), names nformat(number_d2)
putexcel Q2= matrix(Table3PanelB'), names nformat(number_d2)


***********
** Figures
***********
kdensity nwpct_Bottom50, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/Bottom50_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_Bottom50

*Null=data are nornally distributed (reject)
sktest nwpct_Bottom50

kdensity nwpct_pct50to70, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/P5070_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_pct50to70

*Null=data are nornally distributed (reject)
sktest nwpct_pct50to70

kdensity nwpct_pct70to90, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/P7090_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_pct70to90

*Null=data are nornally distributed (reject)
sktest nwpct_pct70to90

kdensity nwpct_pct90to99, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/P9099_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_pct90to99

*Null=data are nornally distributed (fail to reject)
sktest nwpct_pct90to99

kdensity nwpct_pct99to999, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/P99999_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_pct99to999

*Null=data are nornally distributed (reject)
sktest nwpct_pct99to999

kdensity nwpct_TopPt1, norm
 graph export "/fof/dfa/projects/papers/business_cycles/replication_IRF/Table3/Top01_Normal.pdf", as(pdf) name("Graph") replace
qnorm nwpct_TopPt1

*Null=data are nornally distributed (reject)
sktest nwpct_TopPt1

***********************************
** ANNUAL
***********************************
** Just keep 4th quarter obs, generate differences based on that

preserve
keep if quarter_str=="4"
tsset id year

local catlist "Bottom50 TopPt1 pct50to70 pct70to90 pct90to99 pct99to999"

// levels difference (annual)
by id: gen nw_ann_diff = (networth-l.networth)
// levels pct change (annual)
by id: gen nw_ann_pct = 100* (networth-l.networth)/l.networth

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen nw_ann_diff_`cat' = .
    replace nw_ann_diff_`cat' = nw_ann_diff if cat == "`cat'"
    local newvars `newvars' nw_ann_diff_`cat'
}

// Loop through each category and create corresponding variables
foreach cat of local catlist {
    gen nw_ann_pct_`cat' = .
    replace nw_ann_pct_`cat' = nw_ann_pct if cat == "`cat'"
    local newvars `newvars' nw_ann_pct_`cat'
}

tabstat  nw_ann_pct_Bottom50 nw_ann_pct_pct50to70 nw_ann_pct_pct70to90 nw_ann_pct_pct90to99 nw_ann_pct_pct99to999 nw_ann_pct_TopPt1, stat(n mean sd skewness kurtosis)

putexcel Y1= "Annual"
putexcel Y1:AE1, border(bottom) merge hcenter
tabstat  nw_ann_pct_Bottom50 nw_ann_pct_pct50to70 nw_ann_pct_pct70to90 nw_ann_pct_pct90to99 nw_ann_pct_pct99to999 nw_ann_pct_TopPt1, stat(n mean sd skewness kurtosis) save 
return list
*putexcel Y2= matrix(r(StatTotal)'), names nformat(number_d2)
mat Table3Ann = r(StatTotal)

*** autocorrelation (annual)

reg nw_ann_pct_Bottom50 l.nw_ann_pct_Bottom50 
mat acB50=e(b)[1,1] 
reg nw_ann_pct_pct50to70 l.nw_ann_pct_pct50to70 
mat acT5070=e(b)[1,1] 
reg nw_ann_pct_pct70to90 l.nw_ann_pct_pct70to90 
mat acT7090=e(b)[1,1] 
reg nw_ann_pct_pct90to99 l.nw_ann_pct_pct90to99 
mat acT10=e(b)[1,1] 
reg nw_ann_pct_pct99to999 l.nw_ann_pct_pct99to999 
mat acT1=e(b)[1,1] 
reg nw_ann_pct_TopPt1 l.nw_ann_pct_TopPt1 
mat acT01=e(b)[1,1] 
mat ac = acB50,acT5070,acT7090,acT10,acT1,acT01
mat Table3Ann = Table3PanBexp\Table3Ann\ac

*putexcel V2= matrix(Table3PanelB'), names nformat(number_d2)
putexcel Y2= matrix(Table3Ann'), names nformat(number_d2)


restore


